package me.ctidy.assignment06.activity;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import android.widget.Button;
import androidx.appcompat.app.AppCompatActivity;
import android.os.Bundle;
import me.ctidy.assignment06.R;
import me.ctidy.assignment06.util.MySQLiteOpenHelper;

public class SQLiteActivity extends AppCompatActivity {

    private static final String TAG = "CTidy.SQLite";
    private MySQLiteOpenHelper dbHelper;
    private static final String INSERT_USER = "INSERT INTO User(username, password) VALUES(?, ?)";
    private static final String UPDATE_USER = "UPDATE User SET password = ? WHERE username = ?";
    private static final String DELETE_USER = "DELETE FROM User WHERE username = ?";
    private static final String SELECT_USER = "SELECT * FROM User";

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_sqlite);

        dbHelper = new MySQLiteOpenHelper(this, "BookStore.db", null, 1);
        Button btnInsert = findViewById(R.id.db_insert);
        btnInsert.setOnClickListener((v) -> {
            SQLiteDatabase db = dbHelper.getWritableDatabase();

            // 1. 使用 Helper 类 内置的方法
            ContentValues values = new ContentValues();
            values.put("username", "admin");
            values.put("password", 123456);
            db.insert("User", null, values);

            // 2. 执行指定 SQL 语句
            db.execSQL(INSERT_USER, new String[] {"张三", "123456"});
            db.execSQL(INSERT_USER, new String[] {"李四", "654321"});
            db.execSQL(INSERT_USER, new String[] {"王五", "515151"});
            db.execSQL(INSERT_USER, new String[] {"赵六", "987561"});
        });

        Button btnUpdate = findViewById(R.id.db_update);
        btnUpdate.setOnClickListener((v) -> {
        });

        Button btnDelete = findViewById(R.id.db_delete);
        btnDelete.setOnClickListener((v) -> {
        });

        Button btnSelect = findViewById(R.id.db_select);
        btnSelect.setOnClickListener((v) -> {
            SQLiteDatabase db = dbHelper.getWritableDatabase();
            Cursor cursor = db.rawQuery(SELECT_USER, null);
            if (cursor.moveToFirst()) {
                String template = "User(id = %d, username = %s, password = %d)";
                do {
                    int id = cursor.getInt(cursor.getColumnIndex("id"));
                    String username = cursor.getString(cursor.getColumnIndex("username"));
                    int password = cursor.getInt(cursor.getColumnIndex("password"));
                    String record = String.format(template, id, username, password);
                    Log.d(TAG, record);
                } while (cursor.moveToNext());
            }
        });
    }
}
